package com.adtech.core.util;

import java.io.BufferedOutputStream;
import java.io.File;
import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.FileOutputStream;
import java.io.IOException;
import java.io.InputStream;
import java.lang.reflect.InvocationTargetException;
import java.util.ArrayList;
import java.util.Iterator;
import java.util.List;

import org.apache.commons.beanutils.BeanUtils;
import org.apache.poi.hssf.usermodel.*;
import org.apache.poi.hssf.util.HSSFColor;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.CellStyle;
import org.apache.poi.ss.usermodel.Comment;
import org.apache.poi.ss.usermodel.Font;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.ss.util.CellRangeAddress;
import org.apache.poi.ss.util.CellUtil;
import org.apache.poi.xssf.usermodel.XSSFClientAnchor;
import org.apache.poi.xssf.usermodel.XSSFDrawing;
import org.apache.poi.xssf.usermodel.XSSFRichTextString;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

/**
 * Excel写操作帮助类
 *
 * @since 2011-7-29
 * @author Dormin
 *
 * @Copyright (C) AD Tech Workshop All Right Reserved
 */
public class ExcelUtil {

	/**
	 * 根据合并区域重新计算列号
	 * @param row 行
	 * @param column 列号
	 * @return 重新计算的列号
	 */
	public static int getRealColumnIndex(Row row, int column){
		int index = column + getRealColumnOffset(row, column);

		if (row.getCell(index) == null){
			return index;
		} else {
			return index + 1;
		}
	}

	/**
	 * 根据合并区域计算列的偏移量
	 * @param row 行
	 * @param column 列号
	 * @return 列的偏移量
	 */
	public static int getRealColumnOffset(Row row, int column){
		int offset= 0;

		while (isCellInMergedRange(row.getSheet(), row.getRowNum(), column + offset)) {
			offset++;
		}

		return offset;
	}

	/**
	 * 合并单元格
	 * @param sheet 页
	 * @param firstRow 起始行号
	 * @param lastRow 截止行号
	 * @param firstColumn 起始列号
	 * @param lastColumn 截止列号
	 * @return 合并区域的序号
	 */
	public static int addMergedRegion(Sheet sheet, int firstRow, int lastRow, int firstColumn, int lastColumn) {
		return addMergedRegion(sheet, firstRow, lastRow, firstColumn, lastColumn, null);
	}

	/**
	 * 合并单元格并设置合并区域的样式
	 * @param sheet 页
	 * @param firstRow 起始行号
	 * @param lastRow 截止行号
	 * @param firstColumn 起始列号
	 * @param lastColumn 截止列号
	 * @param style 样式
	 * @return 合并区域的序号
	 */
	public static int addMergedRegion(Sheet sheet, int firstRow, int lastRow, int firstColumn, int lastColumn, CellStyle style) {
		CellRangeAddress range = new CellRangeAddress(firstRow, lastRow, firstColumn, lastColumn);
		if (style != null){
			setMergedRegionStyle(sheet, range, style);
		}
		return sheet.addMergedRegion(range);
	}

	/**
	 * 创建字体
	 * 
	 * @param wb 工作薄
	 * @param boldweight 粗体
	 * @param color 颜色
	 * @param size 字号
	 * @return 字体
	 */
	public static Font createFont(Workbook wb, short boldweight, short color, short size) {
		Font font = wb.createFont();
		font.setBoldweight(boldweight);
		font.setColor(color);
		font.setFontHeightInPoints(size);
		return font;
	}
	
	/**
	 * 创建默认字号字体
	 * 
	 * @param wb 工作薄
	 * @param boldweight 粗体
	 * @param color 颜色
	 * @return 字体
	 */
	public static Font createFont(Workbook wb, short boldweight, short color) {
		Font font = wb.createFont();
		font.setBoldweight(boldweight);
		font.setColor(color);
		return font;
	}

	/**
	 * 根据起始行号和列号取得合并单元格的区域
	 * 
	 * @param sheet 页
	 * @param row 合并单元格起始行号
	 * @param column 合并单元格起始列号
	 * @return 合并单元格区域
	 */
	public static CellRangeAddress getMergedRegion(Sheet sheet, int row, int column) {
		for (int i = 0; i < sheet.getNumMergedRegions(); i++) {
			CellRangeAddress region = sheet.getMergedRegion(i);
			if (region.getFirstRow() == row && region.getFirstColumn() == column) {
				return region;
			}
		}

		return null;
	}

	/**
	 * 判断是否在合并单元格区域内
	 * @param sheet 页
	 * @param row 行号
	 * @param column 列号
	 * @return 如果行号和列号指定的单元格在合并区域内，返回true；反之，false
	 */
	public static boolean isCellInMergedRange(Sheet sheet, int row, int column){
		for (int i = 0; i < sheet.getNumMergedRegions(); i++) {
			CellRangeAddress region = sheet.getMergedRegion(i);
			if (region.getFirstRow() <= row && row <= region.getLastRow()
					&& region.getFirstColumn() <= column && column <= region.getLastColumn()) {
				return true;
			}
		}

		return false;
	}

	/**
	 * 设置合并单元格区域的样式
	 * @param sheet 页
	 * @param region 合并区域
	 * @param style 样式
	 */
	public static void setMergedRegionStyle(Sheet sheet, CellRangeAddress region, CellStyle style) {
		for (int i = region.getFirstRow(); i <= region.getLastRow(); i++) {

			Row row = CellUtil.getRow(i, sheet);

			for (int j = region.getFirstColumn(); j <= region.getLastColumn(); j++) {
				Cell cell = CellUtil.getCell(row, j);
				cell.setCellStyle(style);
			}
		}
	}

	/**
	 * 读取单元格对象
	 * <p><B>注意：</B>如果指定的行或单元格不存在，则自动创建改行或单元格</P>
	 * @param sheet 页
	 * @param row 行号
	 * @param column 列号
	 * @return 单元格对象
	 */
	public static Cell getCell(Sheet sheet, int row, int column) {
		Row r = CellUtil.getRow(row, sheet);;
		return CellUtil.getCell(r, column);
	}

	/**
	 * 读取单元格的值
	 * <p><B>注意：</B>如果指定的行或单元格不存在，则自动创建改行</P>
	 * @param sheet 页
	 * @param row 行号
	 * @param column 列号
	 * @return 单元格到值（如果单元格为公式，则以数字形式读取公式的计算结果）
	 */
	public static String getStringCellValue(Sheet sheet, int row, int column) {
		return getStringCellValue(getCell(sheet, row, column));
	}

	/**
	 * 读取单元格的值
	 * @param cell 单元格
	 * @return 单元格到值（如果单元格为公式，则以数字形式读取公式的计算结果）
	 */
	public static String getStringCellValue(Cell cell) {
		return getStringCellValue(cell, Cell.CELL_TYPE_NUMERIC);
	}

	/**
	 * 读取单元格的值
	 * @param cell 单元格
	 * @param formulaCellValueType 公式单元格值类型
	 * @return 单元格到值（如果单元格为公式，则以formulaCellValueType形式读取公式的计算结果）
	 */
	public static String getStringCellValue(Cell cell, int formulaCellValueType) {
		if(cell==null) {
			return null;
		}
		switch (cell.getCellType()) {
		case Cell.CELL_TYPE_STRING:
			return cell.getStringCellValue();
		case Cell.CELL_TYPE_NUMERIC:
			String temp = String.valueOf(cell.getNumericCellValue());
			return temp.replaceAll("\\.(0)+$", "");
		case Cell.CELL_TYPE_FORMULA:
			if (Cell.CELL_TYPE_FORMULA == formulaCellValueType){
				return cell.getCellFormula();
			} else {
				cell.setCellType(formulaCellValueType);
				return getStringCellValue(cell);
			}
		case Cell.CELL_TYPE_BLANK:
		case Cell.CELL_TYPE_BOOLEAN:
		case Cell.CELL_TYPE_ERROR:
		default:
			return "";
		}
	}

	/**
	 * 创建虚线边框单元格样式
	 * @param wb 工作薄
	 * @return 虚线边框样式
	 */
	public static CellStyle createDottedBorderCellStyle(Workbook wb) {
		CellStyle style = wb.createCellStyle();
		style.setBorderBottom(CellStyle.BORDER_DOTTED);// 下边框
		style.setBorderLeft(CellStyle.BORDER_DOTTED);// 左边框
		style.setBorderRight(CellStyle.BORDER_DOTTED);// 右边框
		style.setBorderTop(CellStyle.BORDER_DOTTED);// 上边框
		return style;
	}

	/**
	 * 创建直线边框单元格样式
	 * @param wb 工作薄
	 * @return 直线边框样式
	 */
	public static CellStyle createThinBorderCellStyle(Workbook wb) {
		CellStyle style = wb.createCellStyle();
		style.setBorderBottom(CellStyle.BORDER_THIN);// 下边框
		style.setBorderLeft(CellStyle.BORDER_THIN);// 左边框
		style.setBorderRight(CellStyle.BORDER_THIN);// 右边框
		style.setBorderTop(CellStyle.BORDER_THIN);// 上边框
		style.setAlignment(CellStyle.ALIGN_CENTER);//水平居中对齐
		style.setVerticalAlignment(CellStyle.VERTICAL_CENTER);//垂直居中对齐
		style.setWrapText(true);//自动换行
		return style;
	}

	/**
	 * @function 根据文件名创建Excel对象（根据后缀名自动判断是2003或2007格式）
	 * @param file 完整路径的文件名
	 * @return HSSFWorkbook 或 XSSFWorkbook对象
	 * @throws FileNotFoundException 文件找不到时抛出此异常
	 * @throws IOException 文件读取出错时抛出此异常
	 *
	 * @author Dormin
	 */
	public static Workbook getWorkbook(String file) throws FileNotFoundException, IOException {
		InputStream is = new FileInputStream(file);
		String ext = FileUtil.getFileExtension(file);

		try{
			if ("XLS".equalsIgnoreCase(ext)){
				return new HSSFWorkbook(is);
			} else if ("XLSX".equalsIgnoreCase(ext)){
				return new XSSFWorkbook(is);
			} else {
				return null;
			}
		} finally{
			try {
				is.close();
			} catch (IOException e) {}
		}
	}

	/**
	 * @function 得到Excel文件中的所有Sheet页名称
	 * @param book Excel对象
	 * @return 所有页名称
	 *
	 * @author Dormin
	 */
	public static List<String> getSheetNames(Workbook book){
		List<String> results = new ArrayList<String>();

		int sheetNum = book.getNumberOfSheets();

		for(int i = 0 ; i < sheetNum ; i++) {
			Sheet sheet = book.getSheetAt(i);
			results.add(sheet.getSheetName());
		}

		return results;
	}

	/**
	 * 设置单元格标注
	 * @param cell 单元格
	 * @param message 标注内容
	 */
	public static void setCellComment(Cell cell, String message){
		if (message != null && !"".equals(message.trim())) {
			CellStyle cellStyle = cell.getCellStyle();
			CellStyle errorStyle = ExcelUtil.createThinBorderCellStyle(cell.getRow().getSheet().getWorkbook());
			errorStyle.cloneStyleFrom(cellStyle);
			errorStyle.setFillForegroundColor(HSSFColor.RED.index);
			errorStyle.setFillPattern(CellStyle.SOLID_FOREGROUND);
			cell.setCellStyle(errorStyle);
			if (cell instanceof HSSFCell){
				//excel 2003
				HSSFPatriarch patr = (HSSFPatriarch) ((HSSFSheet) cell.getSheet()).createDrawingPatriarch();
				Comment comment = patr.createComment(new HSSFClientAnchor(0, 0, 0, 0, (short)2, 2, (short)8, 8));
				comment.setString(new HSSFRichTextString(message));
				cell.removeCellComment();
				cell.setCellComment(comment);
			} else {
				//excel 2007
				XSSFDrawing patr = ((XSSFSheet) cell.getSheet()).createDrawingPatriarch();
				Comment comment = patr.createCellComment(new XSSFClientAnchor(0, 0, 0, 0, (short)2, 2, (short)8, 8));
				comment.setString(new XSSFRichTextString(message));
				cell.removeCellComment();
				cell.setCellComment(comment);
			}
		}
	}

	/**
	 * 将Excel对象写入硬盘
	 * @param book
	 * @param filename
	 * @throws IOException
	 */
	public static void write(Workbook book, String filename) throws IOException{
		BufferedOutputStream out = null;

		try {
			File folder = new File(FileUtil.getFilePath(filename));
			if (!folder.exists()){
				folder.mkdir();
			}
			out = new BufferedOutputStream(new FileOutputStream(filename), 4096);
			book.write(out);
		} finally {
			if (out != null){
				try {
					out.close();
				} catch (IOException e) {}
			}
		}
	}

    /**
     * 删除列
     * @param sheet
     * @param index
     * @author Dormin
     */
    public static void removeColumn(Sheet sheet,int index) {
        for (Iterator rowIterator = sheet.rowIterator(); rowIterator.hasNext();) {
            Row row = (Row) rowIterator.next();
            Cell cell = row.getCell(index);
            if(cell == null){
                continue;
            }
            row.removeCell(cell);
        }
        sheet.setColumnWidth(index,0);
    }
	
	public static void main(String[] args) {
		File file = new File("D:\\project\\PNPMS\\WebRoot\\excel\\import\\preplan.xls");
		try {
			Workbook wb = ExcelUtil.getWorkbook(file.getPath());
			//WorkBookHelper wbHelper = new WorkBookHelper(wb);
			CellStyle style = wb.createCellStyle();
			Sheet sheet = wb.getSheetAt(0);
            Row row = sheet.getRow(0);
            Cell cell = row.getCell(0);
            System.out.println(cell.getStringCellValue());
			ExcelUtil.write(wb, "D:\\project\\PNPMS\\WebRoot\\excel\\import\\preplan-2.xls");
		} catch (FileNotFoundException e) {
			e.printStackTrace();
		} catch (IOException e) {
			e.printStackTrace();
		}
	}
	
}
